import pymysql
from dbutils.pooled_db import PooledDB
import mysql.connector
from mysql.connector import Error

# 定义连接参数
pooled_db = PooledDB(
    #数据库驱动模块
    creator=pymysql,
    # 最大连接数
    maxconnections=6,
    # 最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
    mincached=2,
    # 最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
    maxcached=5,
    #当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错
    blocking=True,
    # 主机
    host='localhost',
    # 用户名
    user='root',
    # 密码
    password='123456',
    #数据库
    database='funds',
    # 端口号
    port=3306,
    # 编码
    charset='utf8mb4',
    # 设置数据已字典的形式返回
    cursorclass=pymysql.cursors.DictCursor
)

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection


def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        result = cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
        return result
    except Error as e:
        print(f"The error '{e}' occurred")


def fetch_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")